﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace AJAX_learning
{
	public partial class Chapter7 : System.Web.UI.Page
	{
		//protected override void OnLoad(EventArgs e)
		//{
		//    base.OnLoad(e);

		//    // Создать объект Connection.
		//    string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
		//    var con = new SqlConnection(connectionString);
		//    con.StatisticsEnabled = true;
		//    using (con)
		//    {
		//        con.Open();
		//        lblInfo.Text = "<b>Server Version:</b> " + con.ServerVersion;
		//        lblInfo.Text += "<br /><b>DataBase:</b> " + con.Database;
		//        lblInfo.Text += "<br /><b>Connection Is:</b> " + con.State;
		//        var rstat = con.RetrieveStatistics();
		//        lblInfo.Text += "<br /><b>Info: </b>";
		//        foreach (var item in rstat)
		//        {
		//            lblInfo.Text += "<br /><i>" + "&nbsp;&nbsp;&nbsp;&nbsp;" + ((DictionaryEntry)item).Key + "</i>: " + ((DictionaryEntry)item).Value;
		//        }

		//    }
		//    lblInfo.Text += "<br /><b>Now Connection Is:</b> " + con.State;
		//}

		protected void Button1OnClick(object sender, EventArgs e)
		{
			//var con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
			//var htmlStr = new StringBuilder();
			//var cmd = new SqlCommand("SELECT * FROM Employees", con);
			//con.Open();
			//using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			//{
			//    //while (reader.Read())
			//    //{
			//    //    htmlStr.Append("<li>");
			//    //    htmlStr.Append(reader["TitleOfCourtesy"]);
			//    //    htmlStr.Append(" <b>");
			//    //    htmlStr.Append(reader.GetString(1));
			//    //    htmlStr.Append("</b>, ");
			//    //    htmlStr.Append(reader.GetString(2));
			//    //    htmlStr.Append(" - employee from ");
			//    //    htmlStr.Append(reader.GetDateTime(6).ToString("d"));
			//    //    htmlStr.Append(" Number of hires: ");
			//    //    htmlStr.Append("</li>");
			//    //}
			//    GridView1.DataSource = reader;
			//    GridView1.DataBind();
			//}
			//Label1.Text = htmlStr.ToString();
			//lblInfo.Text = "<br /><b>Now Connection Is:</b> " + con.State;

			string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
			SqlConnection con = new SqlConnection(connectionString);
			string sql = "SELECT Orders.CustomerID, Orders.OrderID, COUNT(UnitPrice) AS Items, " +
							"SUM(UnitPrice * Quantity) AS Total FROM Orders " +
							"INNER JOIN [Order Details] " +
							"ON Orders.OrderID = [Order Details].OrderID " +
							"WHERE Orders.CustomerID = @CustID " +
							"GROUP BY Orders.OrderID, Orders.CustomerID";
			SqlCommand cmd = new SqlCommand(sql, con);
			cmd.Parameters.AddWithValue("@CustID", txtID.Text);
			con.Open();
			SqlDataReader reader = cmd.ExecuteReader();
			GridView1.DataSource = reader;
			GridView1.DataBind();
			reader.Close();
			con.Close();

		}

		protected void Button2OnClick(object sender, EventArgs e)
		{
			string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
			SqlConnection con = new SqlConnection(connectionString);
			// Создать команду для вызова хранимой процедуры InsertEmployee.
			SqlCommand cmd = new SqlCommand("InsertEmployee", con);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
			cmd.Parameters["@TitleOfCourtesy"].Value = txtTitle.Text;
			cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
			cmd.Parameters["@LastName"].Value = txtLastName.Text;
			cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
			cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
			cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
			cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
			con.Open();
			try
			{
				int numAff = cmd.ExecuteNonQuery();
				HtmlContent.Text += String.Format(
				"Inserted <b>{0}</b> record(s)<br />", numAff);
				// Получить вновь сгенерированный идентификатор.
				int empID = (int)cmd.Parameters["@EmployeeID"].Value;
				HtmlContent.Text += "New ID: " + empID.ToString();
			}
			finally
			{
				con.Close();
			}
		}

		protected void InsertButtonOnClick(object sender, EventArgs e)
		{
			var con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
			var cmd1 = new SqlCommand {
			                          	Connection = con,
			                          	CommandText = "INSERT INTO Employees (LastName, FirstName) VALUES ('JoeDouble','Tester')",
			                          	CommandType = CommandType.Text
			                          };
			var cmd2 = new SqlCommand {
			                          	Connection = con,
			                          	CommandText = "INSERT INTO Employees (LastName, FirstName) VALUES ('HarryDouble','Sullivan')",
			                          	CommandType = CommandType.Text
			                          };
			SqlTransaction tran = null;
			try
			{
				con.Open();
				tran = con.BeginTransaction();

				cmd1.Transaction = tran;
				cmd2.Transaction = tran;

				cmd1.ExecuteNonQuery();
				throw new ApplicationException();
				cmd2.ExecuteNonQuery();

				tran.Commit();
			}
			catch (Exception)
			{
				if (tran != null) 
					tran.Rollback();
			}
			finally
			{
				con.Close();
			}
		}

		protected void FactoryButtonOnClick(object sender, EventArgs e)
		{
			DbProviderFactory provider = DbProviderFactories.GetFactory(WebConfigurationManager.AppSettings["factory"]);
			var con = provider.CreateConnection();
			con.ConnectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
			var cmd = provider.CreateCommand();
			cmd.CommandText = WebConfigurationManager.AppSettings["employeeQuery"];
			cmd.CommandType = CommandType.Text;
			cmd.Connection = con;
			DbDataReader reader;
			using (con)
			{
				con.Open();
				reader = cmd.ExecuteReader();
				using (reader)
				{
					GridView2.DataSource = reader;
					GridView2.DataBind();
				}
			}
		}
	}
}